Spreadsheet Operators and Functions
The Finance Spreadsheet in doogiePIM provides a number of predefined (built-in) operators and functions. Operators perform basic operations while functions are used to perform more complex calculations. The operators and functions can be divided into groups as follows:
Arithmetic Operators
Arithmetic operators allow you to perform basic arithmetic operations.
|
Mnemonics |
Operands |
Example and explanation |
|
+ |
Two arguments |
= 5 + B1 Adds the first argument's value to the value of the second argument. |
|
- |
Two arguments |
= A5 - B1 Subtracts the second argument's value from the value of the first one. |
|
* |
Two arguments |
= A2 * A3 Multiplies the first argument's value by the value of the second argument. |
|
/ |
Two arguments |
= A1 / PI() Divides the first argument's value by the value of the second argument. |
|
^ |
Two arguments |
= B1 ^ A2 Raises the first argument's value to the power determined by the second argument's value. |
Logical operators
Logical operators perform comparison operations and return a logical value (TRUE or FALSE) as a result.
|
Mnemonics |
Operands |
Example and explanation |
|
= |
Two arguments of logical types |
= A1=A2 The equality operation. Returns TRUE if the first argument is equal to the second one. Otherwise, returns FALSE. |
|
<> |
Two arguments of logical types |
= A1<>B2 The inequality operation. Returns TRUE if values within the list are not equal. Otherwise, returns FALSE. |
|
< |
Two arguments of logical types |
= A1<B1 The "Less than" operation. Returns TRUE if the first argument is less than the second one. Otherwise, returns FALSE. |
|
> |
Two arguments of logical types |
= A1>100 The "Greater than" operation. Returns TRUE if the first argument is greater than the second one. Otherwise, returns FALSE. |
|
<= |
Two arguments of logical types |
= A2<=C4 The "Less than or equal to" operation. Returns TRUE if the first argument is less than or equal to the second one. Otherwise, returns FALSE. |
|
>= |
Two arguments of logical types |
= A2>=1.57 The "Greater than or equal to" operation. Returns TRUE if the first argument is greater than or equal to the second one. Otherwise, returns FALSE. |
Arithmetic functions
A set of mathematic and trigonometric functions.
|
Mnemonics |
Operands |
Action |
|
ABS |
One argument of double type |
=ABS(B6) Returns the absolute value. |
|
ACOS |
One argument of double type |
=ACOS(B16) Returns the arccosine. |
|
ACOSH |
One argument of double type |
=ACOSH(A1) Returns the inverse hyperbolic cosine. |
|
ASIN |
One argument of double type |
=ASIN(B6) Returns the arcsine. |
|
ASINH |
One argument of double type |
=ASINH(C2) Returns the inverse hyperbolic sine of the argument. |
|
ATAN |
One argument of double type |
=ATAN(C5) Returns the arctangent. |
|
ATAN2 |
Two arguments of double type |
=ATAN2(A1, A2) Returns the arctangent using x- and y- coordinates. |
|
ATANH |
One argument of double type |
=ATANH(D4) Returns the inverse hyperbolic tangent. |
|
CEILING |
Two argument of double type |
=CEILING(2.5, 1) Rounds the first argument up to the nearest multiple based on the significance specified by the second argument. |
|
COS |
One argument of double type |
=COS(A1) Returns the cosine. |
|
COSH |
One argument of double type |
=COSH(D7) Returns the hyperbolic cosine |
|
COUNTIF |
Two arguments: the range of cells and the condition of type string |
=COUNTIF(A1:A7,">2") Counts the number of non blank cells within a range which meet a given condition. |
|
DEGREES |
One argument of double type that specifies angle in radians |
=DEGREES(PI()/2) Converts radians to degrees. |
|
EVEN |
One argument of double type |
=EVEN(-1) Rounds the argument up to the nearest even integer. |
|
EXP |
One argument of double type |
=EXP(C1) Returns the exponent value of the argument |
|
FACT |
One nonnegative argument |
=FACT(2) Returns the factorial of the argument. |
|
FLOOR |
Two arguments of double type |
=FLOOR(-2.5, -2) Rounds the first argument down, towards zero, to the nearest multiple of the significance specified by the second argument. |
|
INT |
Argument of type double |
=INT(E4) Rounds the argument down to the nearest integer. |
|
LN |
One argument of double type |
=LN(C1) Returns the natural logarithm. |
|
LOG |
Two arguments: the number and the base |
=LOG(100, 10) Returns the logarithm of a number to the specified base. |
|
LOG10 |
One argument of double type |
=LOG10(1000) Returns the base-10 logarithm of the argument. |
|
MOD |
Two arguments: a number and divisor |
=MOD(4,3) Returns the remainder after the number is divided by the divisor. |
|
ODD |
One argument of double type |
=ODD(3.5) Rounds the argument up to the nearest odd integer. |
|
PI |
No arguments required |
=PI() Returns the value of Pi. |
|
POWER |
Two arguments of double type: the number and the power |
=POWER(A1,4) Raises the number to the base. |
|
RADIANS |
One argument of type double that specifies angle in degrees. |
=RADIANS(180) Converts degrees to radians. |
|
RAND |
No arguments required |
=RAND() Returns a random number between 0 and 1. |
|
ROUND |
Two arguments: the number of double type and the number of digits |
=ROUND(20.57, 1) Rounds the first argument to the specified number of digits. |
|
ROUNDDOWN |
Two arguments of double type |
=ROUNDDOWN(2.75,0) Rounds the argument toward zero. The first argument specifies a number to round up. The second argument defines the number of digits to which you want to round the first argument. |
|
ROUNDUP |
Two arguments of double type |
=ROUNDUP(-3.42,1) Rounds the argument toward infinity. The first argument specifies a number to round up. The second argument defines the number of digits to which you want to round the first argument. |
|
SIGN |
One argument of double type |
=SIGN(A2) Returns the sign. |
|
SIN |
One argument of double type |
=SIN(A1) Returns the sine. |
|
SINH |
One argument of double type |
=SINH(1) Returns the hyperbolic sine. |
|
SQRT |
One argument of double type |
=SQRT(B5) Returns the square root. |
|
SUM |
A list of arguments |
=SUM(A1:C12) =SUM(A1, 3.14, 1.57) Sums all the values in the list. |
|
SUMSQ |
A list of arguments |
=SUMSQ(B1:D1) =SUM(1, 2, 3, 4) Sums the square of values in the list |
|
TAN |
One argument of double type |
=TAN(C3) Returns the tangent. |
|
TANH |
One argument of double type |
=TANH(0) Returns the hyperbolic tangent. |
|
TRUNC |
One argument of double type |
=TRUNC(PI()) Returns the integer part. Compare this with the Int function, which returns a double |
Statistical functions
Basic statistical functions.
|
Mnemonics |
Operands |
Example and explanation |
|
AVERAGE |
A list of arguments |
=AVERAGE(A1:A5) =AVERAGE(10.3, 9.1) Calculates the average value of values within the list. |
|
AVERAGEA |
A list of arguments |
=AVERAGEA(A1:A5) Calculates the average value of the non-empty cells referenced. AVERAGEA(range) equals SUM(range)/COUNTA(range). |
|
COUNT |
A list of arguments |
=COUNT(A1:A4) =COUNT(1, 5, 8) Returns the number of cells in a given range. |
|
COUNTA |
A list of arguments |
=COUNTA(A1:A4) Counts the number of non-empty cells in a given range. |
|
COUNTBLANK |
A list of arguments |
=COUNTBLANK(A1:E1) Counts the number of empty cells. |
|
MAX |
A list of arguments |
=MAX(A1:D1) =MAX(A1, 100, C2) Returns the largest argument value. |
|
MIN |
A list of arguments |
=MIN(A1:D1) =MIN(0, C2) Returns the smallest argument value. |
|
SUM |
A list of arguments |
=SUM(A1:C12) =SUM(A1, 3.14, 1.57) Sums values within the list. |
|
SUMSQ |
A list of arguments |
=SUMSQ(B1:D1) =SUMSQ(1, 2, 3, 4) Sums squares of values within the list. |
Logical functions
Logical functions take logical values as arguments and return a logical value as a result.
|
Mnemonics |
Operands |
Example and explanation |
|
AND |
A list of logical arguments |
=AND(1<B4, B4<100) Logical AND operation. Returns TRUE if all values within the list are TRUE; returns FALSE if one or more values within the list evaluates to FALSE. |
|
FALSE |
No arguments required |
=FALSE() Returns the logical value FALSE. |
|
IF |
Three arguments: the logical expression, the value to return if the expression succeeds, the value to return the expression fails. |
=IF(A10<=100, "Within budget", "Over budget") Returns the second argument if the logical expression evaluates to TRUE and the third argument otherwise. |
|
NOT |
One argument of logical type |
=NOT(1>D4) Logical NOT operation. Reverses the value of its argument. |
|
OR |
A list of logical arguments |
=OR(A1>=10, A1<=10) Logical OR operation. Returns TRUE if any argument is TRUE; returns FALSE if all values within the list evaluate to FALSE. |
|
TRUE |
No arguments required. |
=TRUE() Returns the logical value TRUE. |
Date and Time functions
|
Mnemonics |
Operands |
Example and explanation |
|
DATE |
Three operands defining the year, month and day. |
=DATE(1900,1,1) Calculates the serial number that represents a specified date. |
|
DAY |
One argument defining the serial number of the required date. |
=DAY(TODAY()) Returns the day portion of a given date. |
|
HOUR |
One argument defining the serial number of the required date/time value. |
=HOUR(NOW()) Returns the hour portion of a given date/time value. |
|
MONTH |
One argument defining the serial number of the required date. |
=MONTH(TODAY()) Returns the month portion of a given date. |
|
MINUTE |
One argument defining the serial number of the required date/time value. |
=MINUTE(NOW()) Returns the minutes portion of a given date/time value. |
|
NOW |
No arguments required. |
=NOW() Returns the current time in general format. You can apply further formatting to the result of the function. |
|
SECOND |
One argument defining the serial number of the required date/time value. |
=SECOND(NOW()) Returns the seconds portion of a given date/time value. |
|
TIME |
Three arguments defining hour, minute and second parts of a time value. |
=TIME(16, 48, 10) Returns a decimal number for a specified time. |
|
TODAY |
No arguments required. |
=TODAY() Returns the serial number of the current date. |
|
WEEKDAY |
Two arguments: the serial number of the required date, weekday base. |
=WEEKDAY(TODAY()) =WEEKDAY(DATE( 2002, 12, 1),1) Returns the day of the week corresponding to the specified date. The weekday base identifies the first day of the week and determines the return value type: 1 or omitted: the first day of the week is Sunday. The function returns 1 for Sunday, 2 for Monday, etc. 2: the first day of the week is Monday. The function returns 1 for Monday, 2 for Tuesday, etc. 3: the first day of the week is Monday. The function returns 0 for Monday, 1 for Tuesday, etc. |
|
YEAR |
One argument defining the serial number of the required date. |
=YEAR(TODAY()) Returns the year portion of a given date. |
Text functions
|
Key |
Operands |
Example and explanation |
|
& (ampersand) |
Two arguments of type string |
= "doogie"&"PIM" Concatenates specified strings. |
|
CONCATENATE |
A list of strings |
=CONCATENATE("BiteSpire", "Software") Joins several text strings in one text string. An alternative to "&". |
|
DOLLAR |
Two arguments defining the value and the number of digits to the right of the decimal point in the output string. |
=DOLLAR(957.344, 2) Converts the number to text using currency format $#,##0.00_);($#,##0.00), with the decimals rounded to the specified number of places. |
|
FIXED |
The first argument of type double is required. The second argument of type integer is optional (the default value is 2). The third argument of type Boolean is optional |
=FIXED(1234.567, 1) Rounds the first argument to the number of decimals determined by the second argument and returns it as a string. The third parameter specifies whether to omit commas in the output string. |
|
LEFT |
The first argument of type string is required. The second argument of type integer is optional. |
=LEFT(A1) =LEFT(A1, 3) Returns the first character or characters in a text string. The second parameter defines the number of characters to extract. The default value is 1 |
|
LEN |
One argument of type string. |
=LEN("ABC") Returns the length of a given string. |
|
LOWER |
One argument of type string. |
=LOWER(A1) Converts a string to lowercase. |
|
MID |
The first argument is of type string, the second and the third arguments are of type integer. |
=MID("ABC",3,1) Returns the substring of a given text string. The position of the substring is defined by the second parameter. The third parameter specifies the number of characters to extract. |
|
RIGHT |
The first argument of type string is required. The second argument of type integer is optional |
=RIGHT(B2, 3) Returns the last character or characters in a text string. The second parameter defines the number of characters to extract. The default value is 1. |
|
TRIM |
One argument of string type |
=TRIM(" BiteSpire Soft ware ") Removes all spaces from text except for single spaces between words. |
|
UPPER |
One argument of type string. |
=UPPER(A1&A2) Converts a string to uppercase. |
"IS" functions
Functions which test the type of a value and return a Boolean result.
|
Mnemonics |
Operands |
Example and explanation |
|
ISBLANK |
A value of any type |
=ISBLANK(A1) Returns TRUE if cell is empty. |
|
ISERR |
A value of any type |
=ISERR(H1) Returns TRUE if the cell contains any error value except #N/A. |
|
ISERROR |
A value of any type |
=ISERROR(A7) Returns TRUE if the cell contains any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!). |
|
ISLOGICAL |
A value of any type |
=ISLOGICAL(C3) Returns TRUE if the specified value refers to a logical value. |
|
ISNA |
A value of any type |
=ISNA(E5) Returns TRUE if the cell contains #N/A (value not available) error value. |
|
ISNONTEXT |
A value of any type |
=ISNONTEXT(D4) Returns TRUE if the cell does not contain text. Returns TRUE for blank cells |
|
ISNUMBER |
A value of any type |
=ISNUMBER(A2) Returns TRUE if the cell contains a number. |
|
ISTEXT |
A value of any type |
=ISTEXT(D1) Returns TRUE if the specified cell contains text. |
Related Topics